package com.wj.dao.impl;

import java.util.List;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;

import com.wj.bean.Car;
import com.wj.bean.OrderList;
import com.wj.bean.Orders;
import com.wj.bean.Room;
import com.wj.bean.User;
import com.wj.dao.BaseDAO;
import com.wj.dao.DAO;

public class DAOImpl extends BaseDAO implements DAO {

	@Override
	public List<User> queryUser() {
		return getJdbcTemplate().query("select * from users", new BeanPropertyRowMapper<>(User.class));
	}

	@Override
	public void addUser(User u) {
		getJdbcTemplate().update("insert into users(u_name,u_pass,u_phone) values(?,?,?)",u.getU_name(),u.getU_pass(),u.getU_phone());
	}

	@Override
	public List<User> listAllSearch(String search, int offset, int limit) {
		return getJdbcTemplate().query("select * from users where concat(u_name,u_phone) like ? limit ?,?", new BeanPropertyRowMapper<>(User.class),"%"+search+"%",offset,limit);
	}

	@Override
	public int getTotal() {
		return getJdbcTemplate().queryForObject("select count(*) from users",Integer.class);
	}

	@Override
	public User queryOneUser(String name, String pass) {
		try {
			return getJdbcTemplate().queryForObject("select * from users where u_name = ? and u_pass =?", new BeanPropertyRowMapper<>(User.class),name,pass);
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}

	@Override
	public boolean removeUserById(int u_id) {
		return getJdbcTemplate().update("delete from users where u_id=?",u_id)>0;
	}

	@Override
	public int removeAllUser(List<String> list) {
		StringBuffer sql = new StringBuffer("delete from users where u_id in (");
		for (String string : list) {
			sql.append("?,");
		}
		sql.deleteCharAt(sql.length()-1);
		sql.append(")");
		return getJdbcTemplate().update(sql.toString(),list.toArray());
	}

	@Override
	public List<Room> listAllRoom(String search, int offset, int limit) {
		return getJdbcTemplate().query("select * from room where concat(r_num,r_type,r_price) like ? limit ?,?", new BeanPropertyRowMapper<>(Room.class),"%"+search+"%",offset,limit);
	}

	@Override
	public int getRoomTotal() {
		return getJdbcTemplate().queryForObject("select count(*) from room",Integer.class);
	}

	@Override
	public boolean updUserById(User u) {
		return getJdbcTemplate().update("update users set u_name = ?,u_pass = ?,u_phone = ? where u_id = ?",u.getU_name(),u.getU_pass(),u.getU_phone(),u.getU_id())>0;
	}

	@Override
	public int updRoomByType(String r_type, int r_price) {
		return getJdbcTemplate().update("update room set r_price = ? where r_type = ?",r_price,r_type);
	}

	@Override
	public List<String> queryRoomByType(String type) {
		List<String> list=getJdbcTemplate().queryForList("select r_num from room  where r_type like ? and r_order = '否'",String.class,"%"+type+"%");
		return list;
	}

	@Override
	public int queryRoomByTypeToPrice(String type) {
		return getJdbcTemplate().queryForObject("select r_price from room where r_type like ? group by r_type", Integer.class,"%"+type+"%");
	}

	@Override
	public int queRoomByNumForId(String r_num) {
		// TODO Auto-generated method stub
		return getJdbcTemplate().queryForObject("select r_id from room where r_num = ?", Integer.class,r_num);
	}

	@Override
	public boolean updRoomByNum(String r_num) {
		// TODO Auto-generated method stub
		return getJdbcTemplate().update("update room set r_order = '是' where r_num = ?",r_num)>0;
	}

	@Override
	public boolean addRoomOrder(Orders o) {
		// TODO Auto-generated method stub
		return getJdbcTemplate().update("insert into orders(o_date,o_days,o_price,u_id,r_id) values(?,?,?,?,?)",o.getO_date(),o.getO_days(),o.getO_price(),o.getU_id(),o.getR_id())>0;
	}

	@Override
	public List<OrderList> listAllOrder(String search, int offset, int limit) {
		// TODO Auto-generated method stub
		return getJdbcTemplate().query("select * from (orders,room) inner join users on users.u_id = orders.u_id  \r\n" + 
				"and room.r_id=orders.r_id where concat(u_name,r_num) like ? limit ?,?", new BeanPropertyRowMapper<>(OrderList.class),"%"+search+"%",offset,limit);
	}

	@Override
	public int getOrderTotal() {
		// TODO Auto-generated method stub
		return getJdbcTemplate().queryForObject("select count(*) from orders",Integer.class);
	}

	@Override
	public boolean removeOrderById(int o_id) {
		// TODO Auto-generated method stub
		return getJdbcTemplate().update("delete from orders where o_id=?",o_id)>0;
	}

	@Override
	public int removeAllOrder(List<String> list) {
		StringBuffer sql = new StringBuffer("delete from orders where o_id in (");
		for (String string : list) {
			sql.append("?,");
		}
		sql.deleteCharAt(sql.length()-1);
		sql.append(")");
		return getJdbcTemplate().update(sql.toString(),list.toArray());
	}

	@Override
	public int queryRoomByNum(String r_num) {
		// TODO Auto-generated method stub
		return getJdbcTemplate().queryForObject("select r_id from room where r_num = ?",Integer.class,r_num);
	}

	@Override
	public boolean updOrder(int o_id, int r_id, String o_date, int o_days) {
		// TODO Auto-generated method stub
		return getJdbcTemplate().update("update orders set r_id = ?,o_date = ?,o_days = ? where o_id=?",r_id,o_date,o_days,o_id)>0;
	}

	@Override
	public List<Car> listAllCar() {
		// TODO Auto-generated method stub
		return getJdbcTemplate().query("select * from car", new BeanPropertyRowMapper<>(Car.class));
	}

	@Override
	public int updCarOrder(List<String> c_idlist, String u_id) {
		int k=0;
		for (int i = 0; i < c_idlist.size(); i++) {
			boolean b=getJdbcTemplate().update("update car set u_id = ?,c_order = '是' where c_id=?",u_id,c_idlist.get(i))>0;
			if (b) {
				k++;
			}
		}
		return k;
	}

	@Override
	public List<Orders> queryOrderByUid(String u_id) {
		return getJdbcTemplate().query("select * from orders where u_id = ?", new BeanPropertyRowMapper<>(Orders.class),u_id);
	}

	@Override
	public List<Car> listAllOrderCarBack(String search, int offset, int limit) {
		return getJdbcTemplate().query("select c_id,u_name,c_order from car inner join users where car.u_id=users.u_id and concat(u_name,c_order) like ? limit ?,?", new BeanPropertyRowMapper<>(Car.class),"%"+search+"%",offset,limit);
	}

	@Override
	public int getOrderCarTotal() {
		// TODO Auto-generated method stub
		return getJdbcTemplate().queryForObject("select count(*) from car inner join users where car.u_id=users.u_id",Integer.class);
	}

	@Override
	public List<Car> listAllCarBack(String search, int offset, int limit) {
		List<Car> l=getJdbcTemplate().query("select * from car where concat(c_order) like ? limit ?,?", new BeanPropertyRowMapper<>(Car.class),"%"+search+"%",offset,limit);
		return l;
	}
				
	@Override
	public int getCarTotal() {
		// TODO Auto-generated method stub
		return getJdbcTemplate().queryForObject("select count(*) from car",Integer.class);
	}

	@Override
	public boolean removeCarById(int c_id) {
		return getJdbcTemplate().update("update car set c_order = '否',u_id = null where c_id=?",c_id)>0;
	}

	@Override
	public int removeAllCar(List<String> list) {
		int k=0;
		for (int i = 0; i < list.size(); i++) {
			boolean b=getJdbcTemplate().update("update car set c_order = '否',u_id = null where c_id=?",list.get(i))>0;
			if (b) {
				k++;
			}
		}
		return k;
	}
}
